/******************************************************************************
 * Copyright (C) 2014 ShenZhen YiHua Computer Co., Ltd.
 * All Rights Reserved.
 * 本软件为深圳怡化电脑股份有限公司开发研制。未经本公司正式书面同意，其他任何个人、团体
 * 不得使用、复制、修改或发布本软件.
 *****************************************************************************/
package net.dgg.zz.common.utils;

import net.dgg.iboss.base.util.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelTool {
    /**
     * *
     *
     * @param filename            保存到客户端的文件名
     * @param title               标题行 例：String[]{"名称","地址"}
     * @param key                 从查询结果List取得的MAP的KEY顺序，需要和title顺序匹配，例：String[]{"name","address"}
     * @param values              结果集
     * @param httpServletResponse
     * @throws IOException
     */
    public static void createN(String filename, String[] title, String[] key,
                               @SuppressWarnings("rawtypes") List<Map> values, HttpServletResponse httpServletResponse) {
        OutputStream servletOutputStream = null;
        try {
//            String filename2 = new String(filename.getBytes("GBK"), "ISO8859-1");
            //servletOutputStream = httpServletResponse.getOutputStream();
            servletOutputStream = new BufferedOutputStream(httpServletResponse.getOutputStream());
            HSSFWorkbook workbook = null;//创建工作簿
//            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + filename2);
//            httpServletResponse.setContentType("application/x-download");
            httpServletResponse.setContentType("application/octet-stream; charset=UTF-8");
//            httpServletResponse.setContentType("application/octet-stream");
            httpServletResponse.setHeader("Content-Disposition", "attachment; filename=\""
                    + new String(filename.getBytes("GBK"), "ISO8859-1") + "\"");
            workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();//创建页
            HSSFRow row = null;
            HSSFCell cell = null;
            row = sheet.createRow((short) 0);
            for (int i = 0; title != null && i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new HSSFRichTextString(title[i]));
            }
            @SuppressWarnings("rawtypes")
            Map map = null;
            for (int i = 0; values != null && i < values.size(); i++) {
                row = sheet.createRow(i + 1);
                map = values.get(i);
                for (int i2 = 0; i2 < key.length; i2++) {
                    cell = row.createCell(i2);
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    if (map.get(key[i2]) == null) {
                        cell.setCellValue(new HSSFRichTextString(""));
                    } else {
                        cell.setCellValue(new HSSFRichTextString(map.get(key[i2]).toString()));
                    }
                }
            }
            workbook.write(servletOutputStream);
           /* //获取一个向Response对象写入数据的流,当tomcat服务器进行响应的时候，会将Response中的数据写给浏览器
            String data = "导出成功";
            OutputStream stream = httpServletResponse.getOutputStream();
            stream.write(data.getBytes("UTF-8"));*/
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != servletOutputStream) {
                    servletOutputStream.flush();
                    servletOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * @param cell
     * @return String    返回类型
     * @Title: changeToString
     * @Description: 将Cell对象转换成String类型（导入时调用）
     */
    public static String cellValue(Cell cell) {
        String strCell = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    strCell = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date d = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                        //DateFormat format= new SimpleDateForma;
                        strCell = formater.format(d);
                        //只有日期的转化
                        strCell = strCell.contains("00:00") ? strCell.substring(0, 10) : strCell;
                    } else {
                        strCell = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    strCell = cell.getStringCellValue();
                    break;
                default:
            }
        }
        return strCell.trim();
    }


    /**
     * @param row
     * @param cellIndex
     * @return String    返回类型
     * @Title: cellToString
     * @Description: 根据索引返回行中某个单元格的值
     */
    public static String cellValue(Row row, int cellIndex) {
        return cellValue((Cell) row.getCell(cellIndex));
    }

    /**
     * @param row
     * @param cellIndex
     * @return String    返回类型
     * @Title: cellValueStr
     * @Description: 读取单元格的文本，如果是数字将替换“.0”
     */
    public static String cellValueStr(Row row, int cellIndex) {
        String value = cellValue((Cell) row.getCell(cellIndex));
        if (StringUtils.isNotBlank(value))
            value = value.replace(".0", "");
        return value;
    }

    /**
     * @param row
     * @param cellIndex
     * @param title
     * @return String    返回类型
     * @Title: cellTitleCheck
     * @Description: 检查Excel文件的某个列的列名是否与传入的title一致
     */
    public static String cellTitleCheck(Row row, int cellIndex, String title) {
        String result = "";
        String s = ExcelTool.cellValue(row, cellIndex);
        if (!StringUtils.isNotBlank(s) || !s.equals(title)) {
            result = "第" + (cellIndex + 1) + "列必须为[" + title + "]列<br/>";
        }
        return result;
    }


    /**
     * @param row        行对象
     * @param cellIndex  列索引
     * @param objErr     错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title      字段名
     * @return boolean    返回类型
     * @Title: cellValueBlank
     * @Description: Excel单元格非空检查
     */
    public static boolean cellValueNotBlank(Row row, int cellIndex, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s.replace(".0", "");
        if (StringUtils.isNotBlank(s)) {
        } else {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "不能为空\r\n";
        }
        return result;
    }

    /**
     * @param row        行对象
     * @param cellIndex  列索引
     * @param objErr     错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title      字段名
     * @return boolean    返回类型
     * @Title: cellValueBlank
     * @Description: Excel单元格非空检查(字符不包含小数)
     */
    public static boolean cellTextValueNotBlank(Row row, int cellIndex, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        ;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s.replace(".0", "");
        if (StringUtils.isNotBlank(s)) {
        } else {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "不能为空\r\n";
        }
        return result;
    }

    /**
     * @param cellValue
     * @param dbValue
     * @param objErr
     * @param errorIndex
     * @param title
     * @return boolean    返回类型
     * @Title: cellValueEqualsDbValue
     * @Description: 比较两个字符串的值，并记录错误信息
     */
    public static boolean cellValueEqualsDbValue(String cellValue, String dbValue, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        if (!cellValue.equals(dbValue)) {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "与数据库中数据不一致！\r\n";
        }
        return result;
    }

    /**
     * @param row        行对象
     * @param cellIndex  列索引
     * @param objErr     错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title      字段名
     * @return boolean    返回类型
     * @Title: cellValueNotBlankNumber
     * @Description: Excel单元格非空、电子邮件地址检查
     */
    public static boolean cellValueNotBlankEmail(Row row, int cellIndex, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s;
        if (StringUtils.isNotBlank(s)) {
            if (StringUtils.isValidEmail(s)) {
            } else {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为邮箱地址的" + title + "\r\n";
            }
        } else {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "不能为空\r\n";
        }
        return result;
    }

    /**
     * @param row        行对象
     * @param cellIndex  列索引
     * @param objErr     错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title      字段名
     * @return boolean    返回类型
     * @Title: cellValueNotBlankMobile
     * @Description: Excel单元格非空、手机号码地址检查
     */
    public static boolean cellValueNotBlankMobile(Row row, int cellIndex, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        BigDecimal db = new BigDecimal(s);//避免电话号码读出来为科学计数法
        s = db.toPlainString();
        objErr[cellIndex] = s + "";
        if (StringUtils.isNotBlank(s)) {
            if (StringUtils.isValidMobileNO(s)) {
            } else {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为手机号码的" + title + "\r\n";
            }
        } else {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "不能为空\r\n";
        }
        return result;
    }
}
